﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Dapper;
using Oracle.ManagedDataAccess.Client;

namespace DevelopTool.Dal
{
    /// <summary>
    /// Oracle数据库管理
    /// </summary>
    public class OracleDbDal
    {
        #region sql常量

        //参考：https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_1003.htm#REFRN20003

        #region 数据库实例

        /// <summary>
        /// 查询当前用户数据库实例名称sql
        /// </summary>
        private readonly string GetNowUserDatabaseName_Sql = "SELECT U.USERNAME AS DATABASENAME,U.USER_ID,U.CREATED FROM USER_USERS U WHERE U.USERNAME IS NOT NULL";

        /// <summary>
        /// 查询所有数据库实例(表空间)名称sql
        /// </summary>
        private readonly string GetAllDatabase_Sql = @"SELECT DISTINCT U.USERNAME AS DATABASENAME, 0 AS TABLECOUNT  
                                                        FROM (  
                                                            SELECT USERNAME FROM USER_USERS  
                                                            UNION  
                                                            SELECT USERNAME FROM ALL_USERS  
                                                        ) U  
                                                        WHERE U.USERNAME IS NOT NULL";
        #endregion 数据库实例

        #region 表

        /// <summary>
        /// 查询表sql
        /// </summary>
        private readonly string GetTable_Sql = $@"SELECT T.TABLE_NAME,
                                                   NVL(T.NUM_ROWS, 0) AS NUM_ROWS,
                                                   T.OWNER,
                                                   T.TABLESPACE_NAME,
                                                   T.LAST_ANALYZED,
                                                   C.COMMENTS AS COMMENTS,
                                                   '' AS TEXT,
                                                   '' AS REFCOUNT
                                              FROM ALL_TABLES T
                                              LEFT JOIN ALL_TAB_COMMENTS C
                                                ON T.OWNER = C.OWNER
                                               AND T.TABLE_NAME = C.TABLE_NAME
                                             WHERE 1 = 1";

        /// <summary>
        /// 查询表数量sql
        /// </summary>
        private readonly string GetTableCount_Sql = $@"SELECT COUNT(1) FROM  ALL_TABLES T WHERE 1=1";

        #endregion 表

        #region 表字段
        /// <summary>
        /// 查询表字段sql
        /// </summary>
        private readonly string GetTableColumn_Sql = @"SELECT C.COMMENTS AS COMMENTS,
                                                       T.COLUMN_NAME,
                                                       '' AS NUM_ROWS,
                                                       T.TABLE_NAME,
                                                       T.OWNER,
                                                       T.DATA_TYPE,
                                                       T.DATA_DEFAULT,
                                                       decode(T.NULLABLE, 'Y', '是', 'N', '否', T.NULLABLE) NULLABLE,
                                                       T.DATA_LENGTH,
                                                       T.DATA_PRECISION,
                                                       T.DATA_SCALE,
                                                       T.COLUMN_ID,
                                                       T.HIDDEN_COLUMN,
                                                       T.VIRTUAL_COLUMN
                                                  FROM ALL_TAB_COLS T
                                                  LEFT JOIN ALL_COL_COMMENTS C
                                                    ON C.OWNER = T.OWNER
                                                   AND C.TABLE_NAME = T.TABLE_NAME
                                                   AND C.COLUMN_NAME = T.COLUMN_NAME
                                                 WHERE EXISTS (SELECT 1
                                                          FROM ALL_TABLES
                                                         WHERE OWNER = T.OWNER
                                                           AND TABLE_NAME = T.TABLE_NAME)";


        #endregion 表字段

        #region 视图字段
        /// <summary>
        /// 查询视图字段sql
        /// </summary>
        private readonly string GetViewColumn_Sql = @"SELECT C.COMMENTS AS COMMENTS,
                                                   T.COLUMN_NAME,
                                                   '' AS NUM_ROWS,
                                                   T.TABLE_NAME,
                                                   T.OWNER,
                                                   T.DATA_TYPE,
                                                   T.DATA_LENGTH,
                                                   T.DATA_PRECISION,
                                                   T.DATA_SCALE,
                                                   T.COLUMN_ID,
                                                   T.HIDDEN_COLUMN,
                                                   T.VIRTUAL_COLUMN
                                              FROM ALL_TAB_COLS T
                                              LEFT JOIN ALL_COL_COMMENTS C
                                                ON C.OWNER = T.OWNER
                                               AND C.TABLE_NAME = T.TABLE_NAME
                                               AND C.COLUMN_NAME = T.COLUMN_NAME
                                             WHERE EXISTS (SELECT 1
                                                      FROM ALL_VIEWS
                                                     WHERE OWNER = T.OWNER
                                                       AND VIEW_NAME = T.TABLE_NAME)";

        #endregion 视图字段

        #region 视图

        /// <summary>
        /// 查询视图sql
        /// </summary>
        private readonly string GetView_Sql = $@"SELECT V.VIEW_NAME,V.OWNER,V.TEXT_LENGTH,V.TEXT,
                                                '' AS REFCOUNT FROM ALL_VIEWS V WHERE 1=1";

        #endregion 视图

        #region 函数
        /// <summary>
        /// 查询函数sql
        /// </summary>
        private readonly string GetFunc_Sql = $@"SELECT '' AS TEXT,0 AS LINE,P.OBJECT_NAME,P.OWNER,
                                            P.AGGREGATE,P.PIPELINED,'' AS REFCOUNT FROM  ALL_PROCEDURES P WHERE P.OBJECT_TYPE='FUNCTION'";


        #endregion 函数

        #region 存储过程
        /// <summary>
        /// 查询存储过程
        /// </summary>
        private readonly string GetPro_Sql = $@"SELECT '' AS TEXT,0 AS LINE,P.OBJECT_NAME,P.OWNER
                                             ,'' AS REFCOUNT FROM  ALL_PROCEDURES P WHERE P.OBJECT_TYPE='PROCEDURE'";



        #endregion 存储过程

        #region 触发器
        /// <summary>
        /// 查询触发器
        /// </summary>
        private readonly string GetTriggers_Sql = $@"SELECT T.OWNER,T.TRIGGER_NAME,T.TRIGGER_TYPE,T.TRIGGERING_EVENT,T.TABLE_OWNER,
                                                    T.BASE_OBJECT_TYPE,T.TABLE_NAME,T.WHEN_CLAUSE,T.STATUS,T.DESCRIPTION,T.ACTION_TYPE,
                                                    T.TRIGGER_BODY,'' AS REFCOUNT FROM ALL_TRIGGERS T WHERE 1=1";

        #endregion 触发器

        #region 同义词
        /// <summary>
        /// 查询同义词sql
        /// </summary>
        private readonly string GetSynonyms_Sql = $@"SELECT '' AS TEXT,S.OWNER,S.SYNONYM_NAME,S.TABLE_OWNER,S.TABLE_NAME,S.DB_LINK
                                                    ,'' AS REFCOUNT FROM ALL_SYNONYMS S WHERE 1=1";

        #endregion 同义词

        #region 包
        /// <summary>
        /// 查询包sql
        /// </summary>
        private readonly string GetPackage_Sql = $@"SELECT DISTINCT '' AS PACKAGE_TEXT,'' AS PACKAGEBODY_TEXT,0 AS PACKAGE_TEXT_LINE,
                                                    0 AS PACKAGEBODY_TEXT_LINE,P.OBJECT_NAME,P.OWNER,P.AGGREGATE,P.PIPELINED,'' AS REFCOUNT 
                                                FROM ALL_PROCEDURES P WHERE P.OBJECT_TYPE='PACKAGE'";

        #endregion 包

        #region 序列
        /// <summary>
        /// 查询序列
        /// </summary>
        private readonly string GetSequences_Sql = $@"SELECT '' AS TEXT,S.SEQUENCE_OWNER AS OWNER,S.SEQUENCE_NAME,S.MIN_VALUE,S.MAX_VALUE,
                                                    S.INCREMENT_BY,S.LAST_NUMBER,'' AS REFCOUNT FROM ALL_SEQUENCES S WHERE 1=1";

        #endregion 序列

        #region 锁表

        /// <summary>
        /// 数据库锁表查询sql
        /// </summary>
        private readonly string DbLockQuery_Sql = @"SELECT 'DB_' || S.INST_ID 服务器,
                                                   VI.INSTANCE_NAME 实例,
                                                   S.USERNAME 用户,
                                                   O.OBJECT_TYPE 锁类型,
                                                   O.OWNER || '.' || O.OBJECT_NAME 锁对象,
                                                   S.SERIAL# 串口号,
                                                   S.SID 会话ID,
                                                   (CASE
                                                     WHEN S.BLOCKING_SESSION IS NOT NULL THEN
                                                      '被 ' || BLOCKING_SESSION || ' 锁住'
                                                     ELSE
                                                      S.LOCKWAIT || BLOCKING_SESSION
                                                   END) 是否被锁,
                                                   S.BLOCKING_SESSION 锁定会话ID,
                                                    RAWTOHEX(LO.ADDR) 内存地址,
                                                   (CASE
                                                     WHEN S.SECONDS_IN_WAIT < 60 THEN
                                                      TO_CHAR(S.SECONDS_IN_WAIT) || '秒'
                                                     WHEN S.SECONDS_IN_WAIT BETWEEN 60 AND 3599 THEN
                                                      TO_CHAR(TRUNC(S.SECONDS_IN_WAIT / 60)) || '分' ||
                                                      TO_CHAR(MOD(S.SECONDS_IN_WAIT, 60)) || '秒'
                                                     WHEN S.SECONDS_IN_WAIT BETWEEN 3600 AND 86399 THEN
                                                      TO_CHAR(TRUNC(S.SECONDS_IN_WAIT / 3600)) || '时' ||
                                                      TO_CHAR(TRUNC(MOD(S.SECONDS_IN_WAIT, 3600) / 60)) || '分'
                                                     ELSE
                                                      TO_CHAR(TRUNC(S.SECONDS_IN_WAIT / 86400)) || '天' ||
                                                      TO_CHAR(TRUNC(MOD(S.SECONDS_IN_WAIT, 86400) / 3600)) || '时'
                                                   END) 锁时长,
                                                   S.SECONDS_IN_WAIT 等待时间,
                                                   SQL_.SQL_TEXT SQL语句,
                                                   L.OS_USER_NAME 系统用户名,
                                                   S.CLIENT_INFO 客户端IP,
                                                   S.MACHINE 客户端名称,
                                                   S.PROGRAM 程序名称,
                                                   SQL_.MODULE || ' ' || SQL_.ACTION || ' ' || S.ACTION 窗口标题
                                              FROM GV$SESSION S
                                              LEFT JOIN GV$SQL SQL_
                                                ON SQL_.SQL_ID = S.SQL_ID
                                               AND SQL_.INST_ID = S.INST_ID
                                              JOIN GV$LOCKED_OBJECT L
                                                ON L.SESSION_ID = S.SID
                                               AND L.INST_ID = S.INST_ID
                                              JOIN ALL_OBJECTS O
                                                ON L.OBJECT_ID = O.OBJECT_ID
                                              JOIN GV$LOCK LO
                                                ON (LO.BLOCK != 0 OR LO.REQUEST != 0)
                                              LEFT JOIN GV$SESSION_LONGOPS A
                                                ON A.INST_ID = S.INST_ID
                                               AND A.SID = S.SID
                                               AND A.SOFAR != A.TOTALWORK
                                              JOIN GV$INSTANCE VI
                                                ON VI.INST_ID = S.INST_ID
                                             WHERE LO.SID = L.SESSION_ID
                                               AND LO.SID = S.SID
                                               AND LO.INST_ID = L.INST_ID
                                               AND LO.INST_ID = S.INST_ID
                                             ORDER BY S.SID, BLOCKING_SESSION ASC";

        #endregion 锁表

        #region 资源
        /// <summary>
        /// 查询资源
        /// </summary>
        private readonly string GetSource_Sql = $@"SELECT V.OWNER,V.VIEW_NAME AS SOURCE_NAME, V.TEXT_LENGTH,V.TEXT,'' COMMENTS,
                                                0 AS ROW_COUNT FROM ALL_VIEWS V WHERE V.OWNER='SYS'";
        #endregion 资源

        #region jobs
        /// <summary>
        /// 查询job任务sql
        /// </summary>
        private readonly string GetJobs_sql = @"SELECT J.JOB,J.LOG_USER,J.PRIV_USER,J.SCHEMA_USER,J.LAST_DATE,J.LAST_SEC,J.THIS_DATE,J.THIS_SEC,
                                                J.NEXT_DATE,J.NEXT_SEC,TO_CHAR(J.TOTAL_TIME,'FM9999999999') AS TOTAL_TIME,decode(J.BROKEN,'Y','启用','N','禁用',J.BROKEN) BROKEN,J.INTERVAL,
                                                J.FAILURES,J.WHAT,J.NLS_ENV,J.MISC_ENV FROM DBA_JOBS J WHERE 1=1";

        #endregion jobs

        #region 用户
        /// <summary>
        /// 查询所有用户
        /// </summary>
        private readonly string GetUsers_Sql = @"SELECT '' AS TEXT, U.USERNAME,U.USER_ID,U.ACCOUNT_STATUS,U.LOCK_DATE,U.EXPIRY_DATE,
                                                U.DEFAULT_TABLESPACE,U.TEMPORARY_TABLESPACE,U.CREATED,U.PASSWORD_VERSIONS,U.PROFILE,
                                                U.EXTERNAL_NAME,U.AUTHENTICATION_TYPE FROM DBA_USERS U WHERE 1=1";

        #endregion 用户

        #endregion


        /// <summary>
        /// 数据库连接串
        /// </summary>
        private string connectionString;

        public OracleDbDal(string connectionString)
        {
            this.connectionString = connectionString;
        }


        /// <summary>
        /// 获取当前用户缺省表空间名
        /// </summary>
        /// <returns></returns>
        public DataTable GetNowUserDataBaseNames()
        {
            return GetDataTable(GetNowUserDatabaseName_Sql);
        }


        /// <summary>
        /// 查询所有数据库实例名称
        /// </summary>
        /// <param name="sqlSugarClient"></param>
        /// <returns></returns>
        public DataTable GetAllDataBases()
        {
            return GetDataTable(GetAllDatabase_Sql);
        }


        /// <summary>
        /// 查询表信息
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dataBase"></param>
        /// <returns></returns>
        public DataTable GetTables(string dataBase = null,string tableName = null)
        {
            StringBuilder sql = new StringBuilder(GetTable_Sql);

            if(!string.IsNullOrEmpty(dataBase))//数据库实例过滤
            {
                sql.Append($" AND UPPER(T.OWNER)='{dataBase.ToUpper()}'");
            }

            if(!string.IsNullOrEmpty(tableName))//表名过滤
            {
                sql.Append($" AND UPPER(T.TABLE_NAME)='{tableName.ToUpper()}'");
            }
            return GetDataTable(sql.ToString());
        }


        /// <summary>
        /// 查询表数量
        /// </summary>
        /// <param name="dataBase"></param>
        /// <returns></returns>
        public int GetTableCount(string dataBase = null)
        {
            StringBuilder sql = new StringBuilder(GetTableCount_Sql);

            if(!string.IsNullOrEmpty(dataBase))//数据库实例过滤
            {
                sql.Append($" AND UPPER(T.OWNER)='{dataBase.ToUpper()}'");
            }

            using(IDbConnection cnn = new OracleConnection(connectionString))
            {
                return cnn.ExecuteScalar<int>(sql.ToString());
            }
        }

        /// <summary>
        /// 查询表-根据表名集合
        /// </summary>
        /// <param name="tableNames"></param>
        /// <param name="dataBase"></param>
        /// <returns></returns>
        public DataTable GetTablesByTableNames(List<string> tableNames,string dataBase = null)
        {
            StringBuilder sql = new StringBuilder(GetTable_Sql);

            if(!string.IsNullOrEmpty(dataBase))//数据库实例过滤
            {
                sql.Append($" AND UPPER(T.OWNER)='{dataBase.ToUpper()}'");
            }

            if(tableNames != null && tableNames.Count > 0)//表名过滤
            {
                var tableNameInStr = "'" + string.Join("'" + ",'",tableNames) + "'";//拼装in条件
                sql.Append($" AND UPPER(T.TABLE_NAME) in ({tableNameInStr.ToUpper()})");
            }
            return GetDataTable(sql.ToString());
        }


        /// <summary>
        /// 表是否存在
        /// </summary>
        /// <param name="dataBaseName"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool ExistTable(string dataBaseName,string tableName)
        {
            string sql = $"SELECT COUNT(1) FROM ALL_TABLES T WHERE T.TABLE_NAME='{tableName}'";
            if(!string.IsNullOrEmpty(dataBaseName))
            {
                sql += $" AND T.OWNER='{dataBaseName}'";
            }

            using(IDbConnection cnn = new OracleConnection(connectionString))
            {
                return cnn.QueryFirstOrDefault<int>(sql,null) > 0;
            }
        }

        /// <summary>
        /// 查询表字段
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dataBase"></param>
        /// <returns></returns>
        public DataTable GetTableColumns(string dataBase = null,string tableName = null,string columnName = null)
        {
            StringBuilder sql = new StringBuilder(GetTableColumn_Sql);

            if(!string.IsNullOrEmpty(dataBase))//数据库实例过滤
            {
                sql.Append($" AND UPPER(T.OWNER)='{dataBase.ToUpper()}'");
            }
            if(!string.IsNullOrEmpty(tableName))//表名过滤
            {
                sql.Append($" AND UPPER(T.TABLE_NAME)='{tableName.ToUpper()}'");
            }
            if(!string.IsNullOrEmpty(columnName))//字段名过滤
            {
                sql.Append($" AND UPPER(T.COLUMN_NAME)='{columnName.ToUpper()}'");
            }
            DataTable dataTable = GetDataTable(sql.ToString());
            dataTable.Columns["NUM_ROWS"].MaxLength = int.MaxValue;
            return dataTable;
        }



        /// <summary>
        /// 查询表字段-根据表名集合
        /// </summary>
        /// <param name="tableNames"></param>
        /// <param name="dataBase"></param>
        /// <returns></returns>
        public DataTable GetTableColumnsByTableNames(List<string> tableNames,string dataBase = null)
        {
            StringBuilder sql = new StringBuilder(GetTableColumn_Sql);

            if(!string.IsNullOrEmpty(dataBase))//数据库实例过滤
            {
                sql.Append($" AND UPPER(T.OWNER)='{dataBase.ToUpper()}'");
            }
            if(tableNames != null && tableNames.Count > 0)//表名过滤
            {
                var tableNameInStr = "'" + string.Join("'" + ",'",tableNames) + "'";//拼装in条件
                sql.Append($" AND UPPER(T.TABLE_NAME) IN ({tableNameInStr.ToUpper()})");
            }

            DataTable dataTable = GetDataTable(sql.ToString());
            dataTable.Columns["NUM_ROWS"].MaxLength = int.MaxValue;
            return dataTable;
        }


        /// <summary>
        /// 查询视图字段
        /// </summary>
        /// <param name="dataBase"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable GetViewColumns(string dataBase = null,string viewName = null,string columnName = null)
        {
            StringBuilder sql = new StringBuilder(GetViewColumn_Sql);
            if(!string.IsNullOrEmpty(dataBase))//数据库实例过滤
            {
                sql.Append($" AND UPPER(T.OWNER)='{dataBase.ToUpper()}'");
            }
            if(!string.IsNullOrEmpty(viewName))//视图名过滤
            {
                sql.Append($" AND UPPER(T.TABLE_NAME)='{viewName.ToUpper()}'");
            }
            if(!string.IsNullOrEmpty(columnName))//字段名过滤
            {
                sql.Append($" AND UPPER(T.COLUMN_NAME)='{columnName.ToUpper()}'");
            }
            DataTable dataTable = GetDataTable(sql.ToString());
            dataTable.Columns["NUM_ROWS"].MaxLength = int.MaxValue;
            return dataTable;
        }


        /// <summary>
        /// 查询字段有效的数据条数
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dataBase"></param>
        /// <returns></returns>
        public int GetColumnDateRowNum(string dataBase,string tableName,string columnName)
        {
            string sql = $"SELECT COUNT({columnName}) FROM {dataBase}.{tableName}";
            try
            {
                using(IDbConnection cnn = new OracleConnection(connectionString))
                {
                    return cnn.QueryFirstOrDefault<int>(sql.ToString(),null);
                }
            }
            catch
            {
                return 0;
            }
        }


        /// <summary>
        /// 查询视图
        /// </summary>
        /// <param name="columnName"></param>
        /// <param name="tableName"></param>
        /// <param name="dataBase"></param>
        /// <returns></returns>
        public DataTable GetViews(string dataBase = null,string viewName = null)
        {
            StringBuilder sql = new StringBuilder(GetView_Sql);

            if(!string.IsNullOrEmpty(dataBase))//数据库实例过滤
            {
                sql.Append($" AND UPPER(V.OWNER)='{dataBase.ToUpper()}'");
            }

            if(!string.IsNullOrEmpty(viewName))//视图名过滤
            {
                sql.Append($" AND UPPER(V.VIEW_NAME)='{viewName.ToUpper()}'");
            }

            return GetDataTable(sql.ToString());

        }



        /// <summary>
        /// 查询函数
        /// </summary>
        /// <param name="funcName"></param>
        /// <param name="dataBase"></param>
        /// <returns></returns>
        public DataTable GetFuncs(string dataBase = null,string funcName = null)
        {
            StringBuilder sql = new StringBuilder(GetFunc_Sql);

            if(!string.IsNullOrEmpty(dataBase))//数据库实例过滤
            {
                sql.Append($" AND UPPER(P.OWNER)='{dataBase.ToUpper()}'");
            }

            if(!string.IsNullOrEmpty(funcName))//函数名过滤
            {
                sql.Append($" AND UPPER(P.OBJECT_NAME)='{funcName.ToUpper()}'");
            }

            return GetDataTable(sql.ToString());

        }


        /// <summary>
        /// 查询存储过程
        /// </summary>
        /// <param name="proName"></param>
        /// <param name="dataBase"></param>
        /// <returns></returns>
        public DataTable GetProcedures(string dataBase = null,string proName = null)
        {
            StringBuilder sql = new StringBuilder(GetPro_Sql);

            if(!string.IsNullOrEmpty(dataBase))//数据库实例过滤
            {
                sql.Append($" AND UPPER(P.OWNER)='{dataBase.ToUpper()}'");
            }

            if(!string.IsNullOrEmpty(proName))//存储过程名过滤
            {
                sql.Append($" AND UPPER(P.OBJECT_NAME)='{proName.ToUpper()}'");
            }

            return GetDataTable(sql.ToString());//存储过程集合
        }


        /// <summary>
        /// 查询触发器
        /// </summary>
        /// <param name="dataBase"></param>
        /// <param name="triggerName"></param>
        /// <returns></returns>
        public DataTable GetTriggers(string dataBase = null,string triggerName = null)
        {
            StringBuilder sql = new StringBuilder(GetTriggers_Sql);

            if(!string.IsNullOrEmpty(dataBase))//数据库实例过滤
            {
                sql.Append($" AND UPPER(T.OWNER)='{dataBase.ToUpper()}'");
            }

            if(!string.IsNullOrEmpty(triggerName))
            {
                sql.Append($" AND UPPER(T.TRIGGER_NAME)='{triggerName.ToUpper()}'");
            }

            return GetDataTable(sql.ToString());
        }

        /// <summary>
        /// 查询所有jobs
        /// </summary>
        /// <returns></returns>
        public DataTable GetJobs(string dataBase = null)
        {
            StringBuilder sql = new StringBuilder(GetJobs_sql);

            if(!string.IsNullOrEmpty(dataBase))//数据库实例过滤
            {
                sql.Append($" AND UPPER(J.SCHEMA_USER)='{dataBase.ToUpper()}'");
            }
            return GetDataTable(sql.ToString());
        }


        /// <summary>
        /// 查询同义词
        /// </summary>
        /// <param name="dataBase"></param>
        /// <param name="synonymName"></param>
        /// <returns></returns>
        public DataTable GetSynonyms(string dataBase = null,string synonymName = null)
        {
            StringBuilder sql = new StringBuilder(GetSynonyms_Sql);

            if(!string.IsNullOrEmpty(dataBase))//数据库实例过滤
            {
                sql.Append($" AND UPPER(S.OWNER)='{dataBase.ToUpper()}'");
            }

            if(!string.IsNullOrEmpty(synonymName))
            {
                sql.Append($" AND UPPER(S.SYNONYM_NAME)='{synonymName.ToUpper()}'");
            }

            return GetDataTable(sql.ToString());
        }


        /// <summary>
        /// 查询所有用户
        /// </summary>
        /// <returns></returns>
        public DataTable GetUsers()
        {
            StringBuilder sql = new StringBuilder(GetUsers_Sql);
            return GetDataTable(sql.ToString());
        }


        /// <summary>
        /// 查询所有资源
        /// </summary>
        /// <returns></returns>
        public DataTable GetSources()
        {
            return GetDataTable(GetSource_Sql);
        }



        /// <summary>
        /// 查询包
        /// </summary>
        /// <param name="dataBase"></param>
        /// <param name="packageName"></param>
        /// <returns></returns>
        public DataTable GetPackages(string dataBase = null,string packageName = null)
        {
            StringBuilder sql = new StringBuilder(GetPackage_Sql);

            if(!string.IsNullOrEmpty(dataBase))//数据库实例过滤
            {
                sql.Append($" AND UPPER(P.OWNER)='{dataBase.ToUpper()}'");
            }

            if(!string.IsNullOrEmpty(packageName))//包名过滤
            {
                sql.Append($" AND UPPER(P.OBJECT_NAME)='{packageName.ToUpper()}'");
            }

            return GetDataTable(sql.ToString());//包集合

        }


        /// <summary>
        /// 查询序列
        /// </summary>
        /// <param name="dataBase"></param>
        /// <param name="sequenceName"></param>
        /// <returns></returns>
        public DataTable GetSequences(string dataBase = null,string sequenceName = null)
        {
            StringBuilder sql = new StringBuilder(GetSequences_Sql);

            if(!string.IsNullOrEmpty(dataBase))//数据库实例过滤
            {
                sql.Append($" AND UPPER(S.SEQUENCE_OWNER)='{dataBase.ToUpper()}'");
            }

            if(!string.IsNullOrEmpty(sequenceName))//名过滤
            {
                sql.Append($" AND UPPER(S.SEQUENCE_NAME)='{sequenceName.ToUpper()}'");
            }

            return GetDataTable(sql.ToString());
        }


        /// <summary>
        /// 获取数据库对象的脚本信息
        /// </summary>
        /// <param name="owner"></param>
        /// <param name="name"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public DataTable GetSourceText(string owner,string name,string type)
        {
            string get_text_sql = $@"SELECT TEXT FROM ALL_SOURCE WHERE OWNER = '{owner}' AND NAME = '{name}' AND TYPE = '{type}' ORDER BY LINE ASC";
            return GetDataTable(get_text_sql);
        }



        /// <summary>
        /// 获取数据类型集合
        /// </summary>
        /// <param name="dataBase"></param>
        /// <returns></returns>
        public List<string> GetDataTypeList(string dataBase = null)
        {
            string sql = "SELECT TT.DATA_TYPE FROM ALL_TAB_COLS TT WHERE 1=1 {0} GROUP BY TT.DATA_TYPE ORDER BY TT.DATA_TYPE ASC";

            if(!string.IsNullOrEmpty(dataBase))
            {
                sql = string.Format(sql,$"AND UPPER(TT.OWNER)='{dataBase.ToUpper()}'");
            }
            else
            {
                sql = string.Format(sql," ");
            }

            using(IDbConnection cnn = new OracleConnection(connectionString))
            {
                return cnn.Query<string>(sql,null).ToList();
            }
        }


        /// <summary>
        /// 查询数据库锁表数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetDbLockData()
        {
            return GetDataTable(DbLockQuery_Sql);
        }


        /// <summary>
        /// 获取指定表/视图数据行数
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public int GetRowCount(string tableName)
        {
            using(IDbConnection cnn = new OracleConnection(connectionString))
            {
                return cnn.QueryFirstOrDefault<int>($"SELECT COUNT(1) FROM {tableName}",null);
            }
        }


        /// <summary>
        /// 测试Oracle数据库连接
        /// </summary>
        /// <param name="connectionConfig"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool TestConnect()
        {
            try
            {
                using(IDbConnection cnn = new OracleConnection(connectionString))
                {
                    //查一下数据库当前时间
                    string time = cnn.QueryFirstOrDefault<string>("SELECT SYSDATE FROM DUAL",null);

                    try
                    {
                        //InitFunc();
                    }
                    catch
                    { }
                    return true;
                }
            }
            catch(Exception)
            {
                return false;
            }

        }


        /// <summary>
        /// 获取依赖关系数量
        /// </summary>
        /// <param name="objectName"></param>
        /// <param name="owner"></param>
        /// <param name="objectType"></param>
        /// <returns></returns>
        public string GetObjectRefCount(string objectType,string objectName,string owner)
        {
            string sql = $@"SELECT (SELECT COUNT(1)
                          FROM ALL_DEPENDENCIES A
                         WHERE A.NAME = '{objectName.ToUpper()}'
                           AND A.OWNER = '{owner.ToUpper()}'
                           AND A.TYPE = '{objectType.ToUpper()}') || '/' ||
                       (SELECT COUNT(1)
                          FROM ALL_DEPENDENCIES A
                         WHERE A.REFERENCED_NAME = '{objectName.ToUpper()}'
                           AND A.REFERENCED_OWNER = '{owner.ToUpper()}'
                           AND A.REFERENCED_TYPE = '{objectType.ToUpper()}')
                  FROM DUAL";

            using(IDbConnection cnn = new OracleConnection(connectionString))
            {
                return cnn.ExecuteScalar<string>(sql);
            }
        }


        /// <summary>
        /// 获取对象依赖关系数据
        /// </summary>
        /// <param name="dataBase"></param>
        /// <param name="name"></param>
        /// <param name="objType"></param>
        /// <param name="dependenciesType">依赖关系类型：1=依赖，2=被依赖</param> 
        /// <returns></returns>
        public DataTable GetReferences(string dataBase,string name,string objType,int dependenciesType)
        {
            string sql = @"SELECT DECODE(D.TYPE,
                        'PROCEDURE','存储过程',
                        'PACKAGE BODY','包体',
                        'PACKAGE','包',
                        'TYPE BODY','类型体',
                        'TRIGGER','触发器',
                        'MATERIALIZED VIEW','物化视图',
                        'SYNONYM','同义词',
                        'VIEW','视图',
                        'FUNCTION','函数',
                        'TYPE','类型',
                        D.TYPE) TYPE,
                DECODE(D.REFERENCED_TYPE,
                        'SEQUENCE','序列',
                        'PROCEDURE','存储过程',
                        'PACKAGE','包',
                        'TABLE','表',
                        'VIEW','视图',
                        'SYNONYM','同义词',
                        'FUNCTION','函数',
                        'INDEXTYPE','索引类型',
                        'INDEX','索引',
                        'TYPE','类型',
                        D.REFERENCED_TYPE) REFERENCED_TYPE,
                D.OWNER,
                D.NAME,
                D.REFERENCED_OWNER,
                D.REFERENCED_NAME,
                D.REFERENCED_LINK_NAME,
                D.DEPENDENCY_TYPE
            FROM ALL_DEPENDENCIES D
            WHERE 1 = 1";

            if(dependenciesType == 1)//依赖
            {
                sql += $" AND D.OWNER='{dataBase}' AND D.NAME='{name}' AND D.TYPE='{objType}'";
            }
            else if(dependenciesType == 2)//被依赖
            {
                sql += $" AND D.REFERENCED_OWNER='{dataBase}' AND D.REFERENCED_NAME='{name}' AND D.REFERENCED_TYPE='{objType}'";
            }
            return GetDataTable(sql);

        }


        /// <summary>
        /// 查询是否存在
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool Exists(string sql)
        {
            using(IDbConnection cnn = new OracleConnection(connectionString))
            {
                return cnn.ExecuteScalar<bool>(sql);
            }
        }

        /// <summary>
        /// 获取行数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int GetCount(string sql)
        {
            using(IDbConnection cnn = new OracleConnection(connectionString))
            {
                return cnn.ExecuteScalar<int>(sql);
            }
        }

        /// <summary>
        /// 执行Oracle sql
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="startRow"></param>
        /// <param name="endRow"></param>
        /// <returns></returns>
        public DataTable ExecuteQuerySql(string sql,long? startRow,long? endRow)
        {
            //分页查询
            if(startRow != null && endRow != null)
            {
                sql = $@"SELECT DEVELOPTOOL_S.*
                                      FROM (SELECT ROWNUM AS DEVELOPTOOL_ROWNO, DEVELOPTOOL_R.*
                                              FROM ({sql}) DEVELOPTOOL_R
                                             WHERE ROWNUM <= {endRow}) DEVELOPTOOL_S
                                     WHERE DEVELOPTOOL_S.DEVELOPTOOL_ROWNO > {startRow}";
            }

            DataTable dataTable = GetDataTable(sql);

            #region 删除DEVELOPTOOL_ROWNO行号

            DataColumn column = dataTable.Columns.Cast<DataColumn>().FirstOrDefault(it =>
            {
                return it.ColumnName == "DEVELOPTOOL_ROWNO";
            });
            if(column != null) dataTable.Columns.Remove(column);
            #endregion 删除DEVELOPTOOL_ROWNO行号

            return dataTable;
        }


        /// <summary>
        /// 执行查询sql获取DataTable，原生方式
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            using(OracleConnection conn = new OracleConnection(connectionString))
            {
                conn.Open();
                OracleCommand command = new OracleCommand(sql,conn)
                {
                    CommandType = CommandType.Text,
                    InitialLONGFetchSize = -1,
                    BindByName = true,
                    InitialLOBFetchSize = -1
                };

                OracleDataReader dataReader = command.ExecuteReader();

                DataTable table = new DataTable("Table");

                for(int i = 0 ; i < dataReader.FieldCount ; i++)
                {
                    table.Columns.Add(dataReader.GetName(i),dataReader.GetFieldType(i));//获取列名
                }

                //循环取数据集合中的数据,存到DataTable中
                do
                {
                    while(dataReader.Read())
                    {
                        DataRow dataRow = table.NewRow();
                        for(int i = 0 ; i < dataReader.FieldCount ; i++)
                        {
                            try
                            {
                                dataRow[i] = dataReader[i];
                            }
                            catch(Exception)
                            {
                                continue;//先跳过转换失败的数据类型，后期再做处理。 已知Oracle的float类型会有问题
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                } while(dataReader.NextResult());

                return table;
            }
        }


        /// <summary>
        /// 执行操作sql，增，删，改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type">1=sql语句，2=存储过程</param>
        /// <returns>受影响行数</returns>
        public int ExecuteOperationSql(string sql,int type = 1)
        {
            CommandType commandType;
            if(type == 1)
            {
                commandType = CommandType.Text;
            }
            else if(type == 2)
            {
                commandType = CommandType.StoredProcedure;
            }
            else
            {
                return 0;
            }

            using(IDbConnection cnn = new OracleConnection(connectionString))
            {
                return cnn.Execute(sql,commandType: commandType);//执行sql
            }
        }




        /// <summary>
        /// 根据表名获取建表sql（DDL）
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public string GetTypeDDLText(string type,string name,string owner)
        {
            using(IDbConnection cnn = new OracleConnection(connectionString))
            {
                string sql = $"SELECT DBMS_METADATA.GET_DDL('{type.ToUpper()}', '{name.ToUpper()}','{owner.ToUpper()}') FROM DUAL";
                try
                {
                    return cnn.ExecuteScalar<string>(sql);
                }
                catch(Exception ex)
                {
                    return ex.Message;
                }
            }
        }
    }
}
